Dataset from the U.S. Small Business Administration (SBA). For this case-study assignment, students assume the role of loan officer at a bank and are asked to approve or deny a loan by assessing its risk of default using logistic regression.
The U.S. SBA was founded in 1953 on the principle of promoting and assisting small enterprises in the U.S. credit market. Small businesses have been a primary source of job creation in the United States; therefore, fostering small business formation and growth has social benefits by creating job opportunities and reducing unemployment. One way SBA assists these small business enterprises is through a loan guarantee program which is designed to encourage banks to grant loans to small businesses. SBA acts much like an insurance provider to reduce the risk for a bank by taking on some of the risk through guaranteeing a portion of the loan. In the case that a loan goes into default, SBA then covers the amount they guaranteed.
There have been many success stories of start-ups receiving SBA loan guarantees such as FedEx and Apple Computer. However, there have also been stories of small businesses and/or start-ups that have defaulted on their SBA-guaranteed loans. The rate of default on these loans has been a source of controversy for decades. Conservative economists believe that credit markets perform efficiently without government participation. Supporters of SBA-guaranteed loans argue that the social benefits of job creation by those small businesses receiving government-guaranteed loans far outweigh the costs incurred from defaulted loans.
Since SBA loans only guarantee a portion of the entire loan balance, banks will incur some losses if a small business defaults on its SBA-guaranteed loan. Therefore, banks are still faced with a difficult choice as to whether they should grant such a loan because of the high risk of default. One way to inform their decision making is through analyzing relevant historical data such as the datasets provided here.
https://amstat.tandfonline.com/doi/full/10.1080/10691898.2018.1434342#.W4TAIi2B00o
from IPython.core.display import display, HTML
display(HTML("""<style> .container {width:96% !important;}</style>"""))
from IPython.display import IFrame
import sys
sys.path.insert(0,'../')
from utils.paths import *
import pandas as pd
import numpy as np
# from plotly.offline import init_notebook_mode, iplot
# import cufflinks as cf
# init_notebook_mode()
# cf.go_offline()
from __future__ import division
path_data = '../large_data_files/ASA_loan_data/'
def table(no):
# there are 13 additional table
return pd.read_csv(path_SBA + 't00%02d'%no + '-10.1080%2F10691898.2018.1434342.csv')
def to_float(x):
x = x.replace('$', '')
x = x.replace(',', '')
x = float(x)
return x
nat = pd.read_csv(path_SBA + 'SBAnational.csv', low_memory=False)
nat = nat[nat.ApprovalFY != '1976A']
nat['ApprovalFY'] = nat.ApprovalFY.astype(int)
# nat = nat[(nat.ApprovalFY >= 1990) & pd.notnull(nat.MIS_Status)]
nat = nat[pd.notnull(nat.MIS_Status)]
nat = nat[pd.notnull(nat.Name)]
# convert to timestamp
nat['ApprovalDate'] = pd.to_datetime(nat['ApprovalDate'], errors = 'coerce')
nat['DisbursementDate'] = pd.to_datetime(nat['DisbursementDate'], errors = 'coerce')
# convert $ to float
nat['DisbursementGross'] = nat['DisbursementGross'].apply(to_float)
nat['BalanceGross'] = nat['BalanceGross'].apply(to_float)
nat['ChgOffPrinGr'] = nat['ChgOffPrinGr'].apply(to_float)
nat['GrAppv'] = nat['GrAppv'].apply(to_float)
nat['SBA_Appv'] = nat['SBA_Appv'].apply(to_float)
nat.shape
nat
nat.head()
nat = nat.sort_values('ApprovalFY')
nat.head()
print nat.Name.nunique()
print nat.shape[0]
loan_record_df.head()
save_csv(loan_record_df, 'loan_record.csv')
!aws s3 ls --human-readable s3://eh-home/ehda-calvin/SBA_study/
loan_record_df = loan_record_df.reset_index()
loan_record_df = loan_record_df.rename(columns = {'index': 'Name'})
loan_record_df.head()
def company_suffix(x):
# classify the business based on the abbreviation after the business name
l = x.split()[-1].replace('.', '').upper()
if l in ['C', 'L', 'I', 'A', 'S', 'D', 'M']:
if 'L L C' in x.upper():
l = 'LLC'
elif len(x) == 1:
l = l
else:
l = x[:-1].split()[-1].replace('.', '').upper()
if l in ['INC', 'INCORPORATED']: # incorporated
abb = 'INC'
elif l in ['CO', 'COMPANY']:
if 'LTD' in x.upper():
abb = 'LTD'
else:
abb = 'CO'
elif l in ['IN']:
abb = 'IN'
elif l in ['LLC']: # limited liability companies
abb = 'LLC'
elif l in ['LLP']: # limited liability companies
abb = 'LLP'
elif l in ['LL']: # limited liability companies
abb = 'LL'
elif l in ['LTD']: # "LTD" or "Ltd." stands for "limited"
abb = 'LTD'
elif l in ['PC']: # professional corporation such as medicine, law and accounting
abb = 'PC'
elif l in ['PLLC']: # professional limited liability company
abb = 'PLLC'
elif l in ['PA']: # professional association
abb = 'PA'
elif l in ['CORP', 'CORPORATION']:
abb = 'CORP'
elif l in ['ASSOC', 'ASSOCIATES']:
abb = 'ASSOC'
elif l in ['DDS']:
abb = 'DDS'
elif l in ['MD', 'CLINIC']:
abb = 'CLINIC'
elif l in ['HOSPITAL']:
abb = 'HOSPITAL'
elif l in ['SALON']:
abb = 'SALON'
elif l in ['CONSTRUCTION']:
abb = 'CONSTRUCTION'
elif l in ['CLEANERS']:
abb = 'CLEANERS'
elif l in ['LAUNDRY', ]:
abb = 'LAUNDRY'
elif l in ['DMD']:
abb = 'DMD'
elif l in ['ENTERPRISES']:
abb = 'ENTERPRISES'
elif l in ['CLUB', 'FOUNDATION', 'FUND', 'INSTITUTE', 'SOCIETY', 'UNION', 'SYNDICATE']:
abb = 'Non-Profit'
elif l in ['RESTAURANT', 'CAFE', 'GRILL']:
abb = 'RESTAURANT'
elif l in ['MARKET', 'MART', 'SUPERMARKET', 'GROCERY', 'DELI', 'PIZZA', 'BAR']:
abb = 'MARKET'
elif l in ['SERVICES', 'SERVICE', 'SERV', 'SERVIC']:
abb = 'SERVICE'
elif l in ['FARM']:
abb = 'FARM'
elif l in ['HOTEL', 'MOTEL', 'INN']:
abb = 'HOTEL'
else:
abb = 'NO SUFFIX'
return abb
loan_record_df['Suffix'] = loan_record_df.Name.apply(company_suffix)
loan_record_df.sample(10)
loan_record_df.head()
save_csv(loan_record_df, 'extra_company_info.csv')
# loan_record_df[loan_record_df.Name.apply(lambda x: x.split()[-1].replace('.', '').upper()).isin(['STORE'])]
def previous_loan(loan_list, year):
return len(filter(lambda x: x > year, loan_list))
def loan_age(loan_start, year):
if year <= loan_start:
age = 0
else:
age = year - loan_start
return age
%%time
print previous_loan(x, 2003)
loan_record_df.head(100).loan_list.apply(previous_loan, args = (1990,)).value_counts()
loan_record_df.head(100).loan_start.apply(loan_age, args = (1990,)).value_counts()
def default(x):
if x == 'CHGOFF':
return 1
else:
return 0
nat['default'] = nat.MIS_Status.apply(default)
nat.ApprovalFY.value_counts().sort_index()
nat[nat.Name == 'ABC HOBBYCRAFT'].T
nat.GrAppv.iplot(kind = 'hist', bins = 100, title = 'Histogram of grant approved', xTitle = 'Grant')
# No. of loan each year
nat.groupby('ApprovalFY').count().max(1).iplot(kind = 'bar', title = 'Loan cases')
(nat[nat.default == 1].groupby('ApprovalFY').count().max(1) /
nat.groupby('ApprovalFY').count().max(1)).iplot(kind = 'bar', title = 'Default rate')
nat[nat.default == 1].groupby('ApprovalFY').SBA_Appv.sum().iplot(kind = 'bar')
nat[nat.default == 1].ChgOffPrinGr.iplot(kind = 'hist', bins = 50)
nat.head().T
nat.State.value_counts().head()
# default rate
nat['default'].sum() / len(nat)
nat.default.value_counts()
LowDoc (Y = Yes, N = No): In order to process more loans efficiently, a “LowDoc Loan” program was implemented where loans under $150,000 can be processed using a one-page application. “Yes” indicates loans with a one-page application, and “No” indicates loans with more information attached to the application. In this dataset, 87.31% are coded as N (No) and 12.31% as Y (Yes) for a total of 99.62%. It is worth noting that 0.38% have other values (0, 1, A, C, R, S); these are data entry errors.
nat.LowDoc.sample(10)
def clean_LowDoc(x):
if x == 'Y':
return 1
elif x == 'N':
return 0
else:
return np.NaN
nat['LowDoc'] = nat.LowDoc.apply(clean_LowDoc)
NAICS (North American Industry Classification System): This is a 2- through 6-digit hierarchical classification system used by Federal statistical agencies in classifying business establishments for the collection, analysis, and presentation of statistical data describing the U.S. economy. The first two digits of the NAICS classification represent the economic sector.
table(3)
nat.NAICS.value_counts().head()
# nat.NAICS.apply(lambda x: len(str(x))).value_counts()
def fix_naics(x):
if x == 0:
naics = '999999'
else:
naics = str(x)
return naics
def naics_sector(x):
if x == '999999':
naics = '999999'
else:
naics = int(str(x)[:2])
if (naics >= 31) and (naics <= 33):
naics = '31'
elif (naics >= 44) and (naics <= 45):
naics = '44'
elif (naics >= 48) and (naics <= 49):
naics = '48'
else:
naics = str(naics)
return naics
nat['NAICS_group'] = nat.NAICS.apply(naics_sector)
nat['NAICS_group'].value_counts().sort_index()
def naics_defaut_rate(x):
default_dict = {'11': 9, '21': 8, '22': 14, '23': 23, '31': 19, '32': 16, '33': 14, '42': 19,
'44': 22, '45': 23, '48': 27, '49': 23, '51': 25, '52': 28, '53': 29, '54': 19,
'55': 10, '56': 24, '61': 24, '62': 10, '71': 21, '72': 22, '81': 20, '92': 15}
naics = str(x)[:2]
if naics not in default_dict.keys():
default_rate = np.NaN
else:
default_rate = default_dict[naics]
return default_rate
nat['NAICS_default_rate'] = nat.NAICS.apply(naics_defaut_rate)
Whether a loan is backed by real estate (possession of land) is another risk indicator that is discussed. The rationale for this indicator is that the value of the land is often large enough to cover the amount of any principal outstanding, thereby reducing the probability of default.
Since the term of the loan is a function of the expected lifetime of the assets, loans backed by real estate will have terms 20 years or greater (≥240 months) and are the only loans granted for such a long term, whereas loans not backed by real estate will have terms less than 20 years (<240 months). Therefore, the authors created a dummy variable, “RealEstate,” where “RealEstate” = 1 if “Term” ≥240 months and “RealEstate” = 0 if “Term” <240 months.
def RealEstate(x):
if x >= 240:
return 1
else:
return 0
nat['RealEstate'] = nat.Term.apply(RealEstate)
nat.head()
The portion which is the percentage of the loan that is guaranteed by SBA (represented as “Portion” in the dataset) is a final risk indicator that is discussed in our courses. This is one of the variables that the authors generated calculating the ratio of the amount of the loan SBA guarantees and the gross amount approved by the bank (SBA_Appv/GrAppv)
nat['SBA_ratio'] = nat.SBA_Appv / nat.GrAppv
nat.SBA_ratio.iplot(kind = 'hist', bins = 10, title = 'Histogram of SBA approved ratio', xTitle = 'SBA_ratio')
nat[nat.default == 1].SBA_ratio.iplot(kind = 'hist', bins = 10)
nat.groupby('default').SBA_ratio.mean()
nat.head().T
Revolving Line of Credit : Y = Yes
nat.RevLineCr.value_counts()
def clean_RevLineCr(x):
if x == 'Y':
return 1
else:
return 0
nat['RevLineCr'] = nat.RevLineCr.apply(clean_RevLineCr)
A ZIP Code is a postal code used by the United States Postal Service (USPS) in a system it introduced in 1963.
The first digit of the ZIP Code is allocated as follows:
0 = Connecticut (CT), Massachusetts (MA), Maine (ME), New Hampshire (NH), New Jersey (NJ), New York (NY, Fishers Island only), Puerto Rico (PR), Rhode Island (RI), Vermont (VT), Virgin Islands (VI), Army Post Office Europe (AE), Fleet Post Office Europe (AE) 1 = Delaware (DE), New York (NY), Pennsylvania (PA) 2 = District of Columbia (DC), Maryland (MD), North Carolina (NC), South Carolina (SC), Virginia (VA), West Virginia (WV) 3 = Alabama (AL), Florida (FL), Georgia (GA), Mississippi (MS), Tennessee (TN), Army Post Office Americas (AA), Fleet Post Office Americas (AA) 4 = Indiana (IN), Kentucky (KY), Michigan (MI), Ohio (OH) 5 = Iowa (IA), Minnesota (MN), Montana (MT), North Dakota (ND), South Dakota (SD), Wisconsin (WI) 6 = Illinois (IL), Kansas (KS), Missouri (MO), Nebraska (NE) 7 = Arkansas (AR), Louisiana (LA), Oklahoma (OK), Texas (TX) 8 = Arizona (AZ), Colorado (CO), Idaho (ID), New Mexico (NM), Nevada (NV), Utah (UT), Wyoming (WY) 9 = Alaska (AK), American Samoa (AS), California (CA), Guam (GU), Hawaii (HI), Marshall Islands (MH), Federated States of Micronesia (FM), Northern Mariana Islands (MP), Oregon (OR), Palau (PW), Washington (WA), Army Post Office Pacific (AP), Fleet Post Office Pacific (AP)
The next two digits represent the sectional center facility (SCF) (e.g. 477xx = Vanderburgh County, Indiana), and the fourth and fifth digits represent the area of the city (if in a metropolitan area), or a village/town (outside metro areas): 47722 (4=Indiana, 77=Vanderburgh County, 22=University of Evansville area). When a sectional center facility's area crosses state lines, that facility is assigned separate three-digit prefixes for the states that it serves.
Connecticut (CT) Massachusetts (MA) Maine (ME) New Hampshire (NH) New Jersey (NJ) New York (NY) Puerto Rico (PR) Rhode Island (RI) Vermont (VT) Virgin Islands (VI) Army Post Office Europe (AE) Fleet Post Office Europe (AE)
'CT','MA','ME','NH','NJ','NY','PR','RI','VT','VI','AE','AE'
nat['Zip_length'] = nat.Zip.apply(lambda x: len(str(x)))
nat['Zip_length'].value_counts()
Seem like some of the zip code has missing values!
def zip_5d(Zip, State):
# fix zip code with error
zero_head = ['CT','MA','ME','NH','NJ','NY','PR','RI','VT','VI','AE','AE']
if (Zip == 9999) or (Zip == 99999):
zip_code = '99999'
elif len(str(Zip)) == 4:
if State in zero_head:
zip_code = '0' + str(Zip)
else:
zip_code = '99999'
elif len(str(Zip)) == 3:
if State in zero_head:
zip_code = '00' + str(Zip)
else:
zip_code = '99999'
elif len(str(Zip)) < 3:
zip_code = '99999'
else:
zip_code = str(Zip)
return zip_code
nat['Zip5d'] = nat.apply(lambda x: zip_5d(x['Zip'], x['State']), axis = 1)
nat[nat['Zip5d'] == '99999'].shape
nat['Zip3d'] = nat.Zip5d.str[:3]
nat['Zip5d'].value_counts().head()
nat['Zip3d'].value_counts().head()
nat = nat[['LoanNr_ChkDgt', 'Name', 'City', 'State', 'Zip5d', 'Zip3d', 'Bank', 'BankState', 'NAICS', 'NAICS_group',
'NAICS_default_rate', 'ApprovalDate', 'ApprovalFY', 'Term', 'NoEmp', 'NewExist', 'CreateJob', 'RetainedJob',
'FranchiseCode', 'UrbanRural', 'RevLineCr', 'LowDoc', 'ChgOffDate', 'DisbursementDate', 'DisbursementGross',
'BalanceGross', 'MIS_Status', 'ChgOffPrinGr', 'GrAppv', 'SBA_Appv', 'default', 'RealEstate', 'SBA_ratio']]
pd.isnull(nat).sum()
nat.to_csv(path_data + 'SBAnational_new.csv', sep = ';', index = False)
!ls -all -h ../large_data_files/ASA_loan_data/
nat03_04 = nat[(nat.ApprovalFY >= 2003) & (nat.ApprovalFY < 2005)]
nat05_06 = nat[(nat.ApprovalFY >= 2005) & (nat.ApprovalFY < 2007)]
nat07_08 = nat[(nat.ApprovalFY >= 2007) & (nat.ApprovalFY < 2009)]
print nat03_04.shape, nat05_06.shape, nat07_08.shape
nat03_04.to_csv('additional_data/nat03_04.csv', index=False)
nat05_06.to_csv('additional_data/nat05_06.csv', index=False)
nat07_08.to_csv('additional_data/nat07_08.csv', index=False)